package com.study.util.excel;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

/**
 * excel2003、2007大数据量解析
 * 
 * @author chenk
 *
 */
public class BigExcelReaderUtil {
	// excel2003扩展名
	private static final String XLS_LOWER_CASE = "xls";

	private static final String XLSX_LOWER_CASE = "xlsx";

	// excel2007扩展名
	private static final String XLS_UPPER_CASE = "XLS";

	private static final String XLSX_UPPER_CASE = "XLSX";

	/**
	 * 根据文件路径解析
	 * 
	 * @param path
	 * @param headerIndex
	 * @param dataIndex
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> readExcelPath(String path, int headerIndex, int dataIndex) throws Exception {
		// 解析数据返回map
		Map<String, Object> map = new HashMap<>();
		// 表头内容
		List<String> header = new ArrayList<>();
		// 数据内容
		Map<Integer, List<List<String>>> listMap = new HashMap<>();
		// 处理excel2003
		if (path.endsWith(XLS_LOWER_CASE) || path.endsWith(XLS_UPPER_CASE)) {
			ExcelXlsReader excelXls = new ExcelXlsReader() {
				@Override
				public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
						List<String> cellList) {
					header.addAll(cellList);
				}

				@Override
				public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
						List<String> cellList) {

					List<String> tempList = new ArrayList<>();
					tempList.addAll(cellList);

					if (listMap.get(sheetIndex) == null) {
						List<List<String>> data = new ArrayList<>();
						data.add(tempList);
						listMap.put(sheetIndex, data);
					} else {
						listMap.get(sheetIndex).add(tempList);
					}
				}

			};

			excelXls.process(path, headerIndex, dataIndex);
			// 处理excel2007
		} else if (path.endsWith(XLSX_LOWER_CASE) || path.endsWith(XLSX_UPPER_CASE)) {
			ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader() {

				@Override
				public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
						List<String> cellList) {
					header.addAll(cellList);
				}

				@Override
				public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
						List<String> cellList) {
					List<String> tempList = new ArrayList<>();
					tempList.addAll(cellList);
					if (listMap.get(sheetIndex) == null) {
						List<List<String>> data = new ArrayList<>();
						data.add(tempList);
						listMap.put(sheetIndex, data);
					} else {
						listMap.get(sheetIndex).add(tempList);
					}
				}
			};

			excelXlsxReader.process(path, headerIndex, dataIndex);

		} else {
			throw new Exception("文件格式错误，上传的excel的扩展名只能是xls或xlsx。");
		}

		map.put("data", listMap);
		map.put("head", header);
		return map;
	}

	/**
	 * 根据MultipartFile文件对象解析
	 * 
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> readExcelFile(MultipartFile file, int headerIndex, int dataIndex)
			throws Exception {
		// 文件判空
		if (file == null) {
			return new HashMap<>();
		}

		// 获取后缀名判断是否为excel
		String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
		if (isNotExcelPath(suffix)) {
			return new HashMap<>();
		}

		// 解析数据返回Map
		Map<String, Object> map = new HashMap<>();
		// 表头内容
		List<String> header = new ArrayList<>();
		// 数据内容
		List<List<String>> data = new ArrayList<>();

		InputStream inputStream = null;
		try {
			inputStream = file.getInputStream();
			if (suffix.endsWith(XLS_LOWER_CASE) || suffix.endsWith(XLS_UPPER_CASE)) {
				// 处理excel2003文件
				ExcelXlsReader excelXls = new ExcelXlsReader() {
					@Override
					public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
							List<String> cellList) {
						// ServiceCheckUtility.errorCheck(sheetIndex > 1,
						// "请删除或清除多余的sheet页，只保留一个正确的sheet页");
						header.addAll(cellList);
					}

					@Override
					public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
							List<String> cellList) {
						List<String> tempList = new ArrayList<>();
						tempList.addAll(cellList);
						data.add(tempList);
					}
				};

				excelXls.process(inputStream, headerIndex, dataIndex);
			} else if (suffix.endsWith(XLSX_LOWER_CASE) || suffix.endsWith(XLSX_UPPER_CASE)) {
				// 处理excel2007文件
				ExcelXlsxReader excelXlsx = new ExcelXlsxReader() {

					@Override
					public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
							List<String> cellList) {
						// ServiceCheckUtility.errorCheck(sheetIndex > 1,
						// "请删除或清除多余的sheet页，只保留一个正确的sheet页");
						header.addAll(cellList);
					}

					@Override
					public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
							List<String> cellList) {
						List<String> tempList = new ArrayList<>();
						tempList.addAll(cellList);
						data.add(tempList);
					}
				};

				excelXlsx.process(inputStream, headerIndex, dataIndex);

			} else {
				throw new Exception("文件格式错误，上传的excel的扩展名只能是xls或xlsx。");
			}
		} finally {
			try {
				if (inputStream != null) {
					inputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		map.put("data", data);
		map.put("head", header);
		return map;
	}

	/**
	 * Excel路径检查
	 * 
	 * @param path
	 * @return
	 */
	private static boolean isNotExcelPath(String path) {
		if (!StringUtils.hasLength(path)) {
			return true;
		}

		return !path.equals(XLS_LOWER_CASE) && !path.endsWith(XLS_UPPER_CASE) && !path.endsWith(XLSX_LOWER_CASE)
				&& !path.endsWith(XLSX_UPPER_CASE);
	}
}
